package com.dldata.devtools30.utils.jdbc;

import com.dldata.devtools30.utils.DBTypeUtil;
import com.dldata.devtools30.utils.DateUtil;
import com.dldata.devtools30.utils.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.*;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

/**
 * jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
 * jdbc.url=jdbc:sqlserver://127.0.0.1:1433;databaseName=MPEHR_YSSJ
 * jdbc.username=sa
 * jdbc.password=123456
 * Created by 呵呵 on 2017-11-28.
 */
public class JdbcDataAcess {

    private final static Logger logger = LoggerFactory.getLogger(JdbcDataAcess.class);

    private String driver;

    private String username;

    private String url;

    private String password;

    public static JdbcDataAcess createJdbcDataAcess(String driver,String url,String username,String password){
        JdbcDataAcess jdbcDataAcess=new JdbcDataAcess();
        jdbcDataAcess.driver = driver;
        jdbcDataAcess.username = username;
        jdbcDataAcess.url = url;
        jdbcDataAcess.password = password;

        Connection connection=null;
        try {
            Class.forName(driver);
            connection=jdbcDataAcess.getConnection();
        } catch (Exception e) {
            logger.error(e.getMessage(),e);
        } finally {
            jdbcDataAcess.closeConnection(connection,null,null);
        }
        return  jdbcDataAcess;
    }

    public List<String> paraseSqlHeader(String sql,List<Object> parameters) throws SQLException {
        DBTypeUtil.DBTYPE dbtype=DBTypeUtil.getDbtype(url);
        int skipNum=0;
//        if(DBTypeUtil.DBTYPE.mysql.equals(dbtype)){
//            sql=sql.replaceAll(";","")+ " limit "+0+" , "+1;
//        }else if(DBTypeUtil.DBTYPE.oracle.equals(dbtype)){
//            StringBuilder tmpStringBuilder= new StringBuilder();
//            tmpStringBuilder.append(" select * from ");
//            tmpStringBuilder.append(" ( ");
//            tmpStringBuilder.append(("select rownum as rownum_id,"));
//            tmpStringBuilder.append(sql.replaceAll(";","").trim().substring(6));
//            tmpStringBuilder.append(" ) ");
//            tmpStringBuilder.append(" where rownum_id between "+1+" and "+ 2);
//            sql=tmpStringBuilder.toString();
//        }else if(DBTypeUtil.DBTYPE.sqlserver.equals(dbtype)){
//            StringBuilder tmpStringBuilder= new StringBuilder();
//            tmpStringBuilder.append(" select top ");
//            tmpStringBuilder.append(1);
//            tmpStringBuilder.append(" ");
//            sql=sql.replace("select",tmpStringBuilder.toString());
//        }else {
//            //TODO:其它数据库
//            throw new SQLException("数据库不支持.");
//        }


        List<String> headerLable= new LinkedList<>();
        Connection connection = null;
        ResultSet resultSet=null;
        PreparedStatement preparedStatement=null;
        try{
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < parameters.size(); i++) {
                Object parameter =  parameters.get(i);
                preparedStatement.setObject(i+1,parameter);
            }
            logger.info("executeQuery: "+sql);
            logger.info("parameters: " + StringUtil.arrayToDelimitedString(parameters.toArray(),","));
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            for (int i=0;i<resultSetMetaData.getColumnCount();i++){
                headerLable.add(resultSetMetaData.getColumnLabel(i+1));
            }
        }catch (Throwable ex){
            logger.error(ex.getMessage(),ex);
            throw ex;
        }finally {
            closeConnection(connection, null, null);
        }
        return headerLable;
    }

    public void closeConnectionAndCommit(Connection connection, ResultSet resultSet, Statement preparedStatement,boolean commit){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement!=null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            if(commit){
                try {
                    if(!connection.getAutoCommit())
                    connection.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }else{
                try {
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void closeConnection(Connection connection, ResultSet resultSet, Statement preparedStatement){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement!=null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                if(!connection.getAutoCommit())
                connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public Connection getConnection() throws SQLException{
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException ex) {
            logger.error(ex.getMessage(),ex);
            throw ex;
        }
    }

    public List<HashMap> dbsearch(String sql){
        return  dbsearch(sql,new LinkedList<>());
    }

    public List<HashMap> dbsearch(String sql,List<Object> parameters){
        List<HashMap> relust = new LinkedList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < parameters.size(); i++) {
                Object parameter = parameters.get(i);
                preparedStatement.setObject(i + 1, parameter);
            }
            logger.info("executeQuery: " + sql);
            logger.info("parameters: " + StringUtil.arrayToDelimitedString(parameters.toArray(), ","));
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            while (resultSet.next()) {
                HashMap<String, Object> stringObjectHashMap = new HashMap<>();
                for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                    String columnName = resultSetMetaData.getColumnLabel(i);
                    switch (resultSetMetaData.getColumnType(i)) {
                        case Types.DATE: {
                            Date anInt = resultSet.getDate(i);
                            String dateString = anInt == null ? "" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, anInt);
                            stringObjectHashMap.put(columnName, dateString);
                            break;
                        }
                        case Types.TIME: {
                            Time time = resultSet.getTime(i);
                            String dateString = time == null ? "" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(time.getTime()));
                            stringObjectHashMap.put(columnName, dateString);
                            break;
                        }
                        case Types.TIMESTAMP: {
                            Object timestamp1 = resultSet.getObject(i);
                            Timestamp timestamp = new Timestamp(0);
                            if (timestamp1 instanceof oracle.sql.TIMESTAMP) {
                                oracle.sql.TIMESTAMP timestampTmp = (oracle.sql.TIMESTAMP) timestamp1;
                                try {
                                    timestamp = timestampTmp.timestampValue();
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            } else if (timestamp1 instanceof oracle.sql.DATE) {
                                oracle.sql.DATE timestampTmp = (oracle.sql.DATE) timestamp1;
                                try {
                                    timestamp = timestampTmp.timestampValue();
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            } else if (timestamp1 instanceof oracle.sql.TIMESTAMPTZ) {
                                oracle.sql.TIMESTAMPTZ timestampTmp = (oracle.sql.TIMESTAMPTZ) timestamp1;
                                try {
                                    timestamp = timestampTmp.timestampValue();
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            } else {
                                timestamp = resultSet.getTimestamp(i);
                            }
                            String dateString = timestamp == null ? "" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(timestamp.getTime()));
                            stringObjectHashMap.put(columnName, dateString);
                            break;
                        }
                        case Types.TINYINT:
                        case Types.SMALLINT:
                        case Types.INTEGER: {
                            ;
                            long anInt = resultSet.getLong(i);
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                        case Types.FLOAT: {
                            ;
                            float anInt = resultSet.getFloat(i);
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                        case Types.NUMERIC:
                        case Types.DECIMAL:
                        case Types.DOUBLE: {
                            ;
                            BigDecimal anInt = resultSet.getBigDecimal(i);
                            BigDecimal[] results = anInt.divideAndRemainder(BigDecimal.valueOf(1));
                            if(results[1].compareTo(new BigDecimal(0))==0){
                                stringObjectHashMap.put(columnName,  results[0]);
                            }else{
                                stringObjectHashMap.put(columnName, anInt);
                            }
                            break;
                        }
                        case Types.CLOB: {
                            ;
                            try {
                                Clob colb = (Clob) resultSet.getObject(i);
                                stringObjectHashMap.put(columnName, colb == null ? "" : colb.getSubString(1, (int) colb.length()));
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }
                        case Types.BLOB: {
                            ;
                            try {
                                Blob blob = (Blob) resultSet.getObject(i);
//                            stringObjectHashMap.put(columnName,new String(blob.getBytes(0, (int) blob.length()),"UTF-8"));
                                //@TODO 后面考虑转二进制可显示串
                                stringObjectHashMap.put(columnName, blob == null ? "" : blob.toString());
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }
                        default: {
                            String anInt = resultSet.getString(i);
                            anInt = anInt == null ? "" : anInt;
                            try {
//                            anInt=URLEncoder.encode(anInt,"UTF-8");
                                //支持1.0数据有编码
//                            anInt= URLDecoder.decode(anInt, "UTF-8");
//                            anInt= EscapeUnescape.escape(anInt);
//                            anInt =  StringUtil.escape(anInt);
                            } catch (Exception e) {
                            }
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                    }
                }
                relust.add(stringObjectHashMap);
            }


        } catch (Exception ex) {
            logger.error(ex.getMessage(),ex);
        }finally {
            closeConnection(connection,null,null);
        }
        return relust;
    }

    public int dbUpdate(String sql){
        return dbUpdate(sql,new LinkedList<>());
    }

    public int dbUpdate(String sql,List<Object> parameters){
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < parameters.size(); i++) {
                Object parameter = parameters.get(i);
                preparedStatement.setObject(i + 1, parameter);
            }
            logger.info("dbUpdate: " + sql);
            logger.info("parameters: " + StringUtil.arrayToDelimitedString(parameters.toArray(), ","));
            return  preparedStatement.executeUpdate();
        } catch (Exception ex) {
            logger.error(ex.getMessage(),ex);
            return -1;
        }finally {
            closeConnection(connection,null,null);
        }
    }

    public String getDriver() {
        return driver;
    }

    public String getUsername() {
        return username;
    }

    public String getUrl() {
        return url;
    }

    public String getPassword() {
        return password;
    }
}
